--region [dbo].[AddPatientSurvey]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddPatientSurvey]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddPatientSurvey]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddPatientSurvey]
GO

CREATE PROCEDURE [dbo].[AddPatientSurvey]
	@PRId int,
	@RecordDate datetime,
	@UpdateDate datetime,
	@SurveyType int,
	@id int OUTPUT
AS

INSERT INTO [PatientSurvey] (
	[PRId],
	[RecordDate],
	[UpdateDate],
	[SurveyType]
) VALUES (
	@PRId,
	@RecordDate,
	@UpdateDate,
	@SurveyType
)

SET @id = SCOPE_IDENTITY()

GO
--endregion


--region [dbo].[UpdatePatientSurvey]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdatePatientSurvey]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdatePatientSurvey]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdatePatientSurvey]
GO

CREATE PROCEDURE [dbo].[UpdatePatientSurvey]
	@Id int,
	@PRId int,
	@RecordDate datetime,
	@UpdateDate datetime
AS

UPDATE [PatientSurvey] SET
	[RecordDate] = @RecordDate,
	[UpdateDate] = @UpdateDate
WHERE
	[Id] = @Id AND [PRId] = @PRId

GO
--endregion


--region [dbo].[DelPatientSurvey]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelPatientSurvey]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelPatientSurvey]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelPatientSurvey]
GO

CREATE PROCEDURE [dbo].[DelPatientSurvey]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[PatientSurvey]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetPatientSurvey]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetPatientSurvey]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetPatientSurvey]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetPatientSurvey]
GO

CREATE PROCEDURE [dbo].[GetPatientSurvey]
	@Id int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[Id],
	[PRId],
	[RecordDate],
	[UpdateDate],
	[SurveyType]
FROM
	[PatientSurvey]
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[GetPatientSurveyCount]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetPatientSurveyCount]
------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID(N'[dbo].[GetPatientSurveyCount]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetPatientSurveyCount]
GO

CREATE PROCEDURE [dbo].[GetPatientSurveyCount]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [PatientSurvey]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL


GO
--endregion



--region [dbo].[GetPatientSurveyList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetPatientSurveyList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetPatientSurveyList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetPatientSurveyList]
GO

CREATE PROCEDURE [dbo].[GetPatientSurveyList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [PatientSurvey]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	[Id],
	[PRId],
	[RecordDate],
	[UpdateDate],
	[SurveyType]
FROM
	[PatientSurvey]
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	WHERE [Id] < 
	( 
	SELECT MIN([Id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Id] 
	FROM [PatientSurvey] ' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion


